{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       id  grade  height\n",
      "wang  101    100     165\n",
      "may   102     90     198\n",
      "john  103     80     178\n",
      "kate  104     95     168\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:1732: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self._setitem_single_block(indexer, value, name)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>grade</th>\n",
       "      <th>height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>wang</th>\n",
       "      <td>101</td>\n",
       "      <td>100</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kate</th>\n",
       "      <td>104</td>\n",
       "      <td>95</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>may</th>\n",
       "      <td>102</td>\n",
       "      <td>90</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>john</th>\n",
       "      <td>103</td>\n",
       "      <td>80</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id  grade  height\n",
       "wang  101    100     165\n",
       "kate  104     95     168\n",
       "may   102     90     198\n",
       "john  103     80     215"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {'id':['101','102','103','104'], 'grade':[100,90,80,95], 'height':[165,198,178,168]}\n",
    "df2 = pd.DataFrame(data_dict, index = ['wang','may','john','kate'])\n",
    "print(df2)\n",
    "# 方法1：\n",
    "# df[] : 列名，行index\n",
    "# df2['id']['wang']\n",
    "\n",
    "# df2['id':'height'] # wrong\n",
    "\n",
    "df2[0:1]['id']\n",
    "\n",
    "# loc\n",
    "df2.loc['wang':'john','height']\n",
    "\n",
    "# iloc 位置\n",
    "df2.iloc[:,2].loc['john'] = 215\n",
    "df2\n",
    "\n",
    "df2.sort_values('grade',ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       id  grade  height\n",
      "wang  101    100     165\n",
      "may   102     90     198\n",
      "john  103     80     178\n",
      "kate  104     95     168\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>grade</th>\n",
       "      <th>height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>wang</td>\n",
       "      <td>100</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>may</td>\n",
       "      <td>90</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>john</td>\n",
       "      <td>80</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>kate</td>\n",
       "      <td>95</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  name  grade  height\n",
       "0  101  wang    100     165\n",
       "1  102   may     90     198\n",
       "2  103  john     80     178\n",
       "3  104  kate     95     168"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {'id':['101','102','103','104'], 'grade':[100,90,80,95], 'height':[165,198,178,168]}\n",
    "df2 = pd.DataFrame(data_dict, index = ['wang','may','john','kate'])\n",
    "print(df2)\n",
    "\n",
    "# 强制转换行、列名称\n",
    "# df2.index.tolist()\n",
    "# df2.index = ['xiaohang','jii','kkk','rrr']\n",
    "# df2.columns=['jiji','d','ee']\n",
    "# df2\n",
    "\n",
    "# df2.rename()\n",
    "\n",
    "# df2.rename(index = {'wang':'xiaowang'}, inplace = True)\n",
    "# df2\n",
    "\n",
    "# df2.rename(columns = {'id':'p-grade'}, inplace = True)\n",
    "# df2\n",
    "# def col_rename(x):\n",
    "#     return x+'_2022'\n",
    "\n",
    "# df2.rename(columns = col_rename, index = col_rename,  inplace = True)\n",
    "# df2\n",
    "# df2['name'] = df2.index.tolist()\n",
    "# df2.index = df2.id.values.tolist()\n",
    "# df2.drop(columns = 'id', inplace = True)\n",
    "# df2\n",
    "df2.index.name = 'name'\n",
    "df2.reset_index(inplace = True)\n",
    "df2.set_index('id',inplace = True)\n",
    "# df2.rename(columns = {'index':'name'}, inplace =True)\n",
    "df2.reset_index(inplace = True)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id\n",
      "wang    101\n",
      "may     102\n",
      "john    103\n",
      "kate    104\n",
      "Name: id, dtype: object\n",
      "***\n",
      "grade\n",
      "wang    100\n",
      "may      90\n",
      "john     80\n",
      "kate     95\n",
      "Name: grade, dtype: int64\n",
      "***\n",
      "height\n",
      "wang    165\n",
      "may     198\n",
      "john    178\n",
      "kate    168\n",
      "Name: height, dtype: int64\n",
      "***\n"
     ]
    }
   ],
   "source": [
    "data_dict = {'id':['101','102','103','104'], 'grade':[100,90,80,95], 'height':[165,198,178,168]}\n",
    "df2 = pd.DataFrame(data_dict, index = ['wang','may','john','kate'])\n",
    "# # print(df2)\n",
    "\n",
    "# for index,row in df2.iterrows():\n",
    "#     print(index)\n",
    "#     print(row['id'])\n",
    "#     print('***')\n",
    "\n",
    "# print('-'*10)\n",
    "# for row in df2.itertuples():\n",
    "#     # print(row)\n",
    "#     print(getattr(row, 'id'), getattr(row, 'height'))\n",
    "#     print('***')\n",
    "\n",
    "\n",
    "for col_name, col in df2.iteritems():\n",
    "    print(col_name)\n",
    "    print(col)\n",
    "    print('***')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['baby_trade_history.csv',\n",
       " 'bond_intraday_trade.csv',\n",
       " 'cbond-interest-info.xlsx',\n",
       " 'cfps2018_famconf_demo.csv',\n",
       " 'meal_order_detail.xlsx',\n",
       " 'sam_tianchi_mum_baby.csv',\n",
       " 'titanic.csv',\n",
       " 'titanic.xlsx']"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### 导入data\n",
    "# project\n",
    "#     - data\n",
    "#         raw-data-folder\n",
    "#         cleaned-data-folder\n",
    "#         cookbook.md\n",
    "        \n",
    "#     - code\n",
    "#         - cleaning-code\n",
    "#     - writing\n",
    "\n",
    "# lecture-python/raw-data\n",
    "\n",
    "# os.chdir('../lecture-python/raw-data')\n",
    "os.getcwd()\n",
    "os.listdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 12 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   PassengerId  891 non-null    object \n",
      " 1   Survived     891 non-null    int64  \n",
      " 2   Pclass       891 non-null    int64  \n",
      " 3   Name         891 non-null    object \n",
      " 4   gender          891 non-null    object \n",
      " 5   Age          714 non-null    float64\n",
      " 6   SibSp        891 non-null    int64  \n",
      " 7   Parch        891 non-null    int64  \n",
      " 8   Ticket       891 non-null    object \n",
      " 9   Fare         891 non-null    float64\n",
      " 10  Cabin        204 non-null    object \n",
      " 11  Embarked     889 non-null    object \n",
      "dtypes: float64(2), int64(4), object(6)\n",
      "memory usage: 83.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df  = pd.read_csv('titanic.csv', dtype = {'PassengerId': str})   \n",
    "    # 结果为dataframe 格式； \n",
    "    # 可加选择\n",
    "        # encoding 默认为 utf-8; other 编码： gbk, gbk2312, gbk18030\n",
    "        # nrow = 100\n",
    "    \n",
    "#pd.read_csv?  # 查看方法说明\n",
    "# df.tail(2)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Braund, Mr. Owen Harris</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5 21171</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C85</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Heikkinen, Miss. Laina</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101282</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>113803</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>C123</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Allen, Mr. William Henry</td>\n",
       "      <td>male</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>373450</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PassengerId  Survived  Pclass  \\\n",
       "0            1         0       3   \n",
       "1            2         1       1   \n",
       "2            3         1       3   \n",
       "3            4         1       1   \n",
       "4            5         0       3   \n",
       "\n",
       "                                                Name     gender   Age  SibSp  \\\n",
       "0                            Braund, Mr. Owen Harris    male  22.0      1   \n",
       "1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "2                             Heikkinen, Miss. Laina  female  26.0      0   \n",
       "3       Futrelle, Mrs. Jacques Heath (Lily May Peel)  female  35.0      1   \n",
       "4                           Allen, Mr. William Henry    male  35.0      0   \n",
       "\n",
       "   Parch            Ticket     Fare Cabin Embarked  \n",
       "0      0         A/5 21171   7.2500   NaN        S  \n",
       "1      0          PC 17599  71.2833   C85        C  \n",
       "2      0  STON/O2. 3101282   7.9250   NaN        S  \n",
       "3      0            113803  53.1000  C123        S  \n",
       "4      0            373450   8.0500   NaN        S  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('titanic.xlsx', sheet_name = 'titanic')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path_famconf = r'C:\\\\Users\\\\illus\\\\OneDrive\\\\B-Database\\\\中国家庭追踪调查-data\\\\CFPS-data\\\\ecfps2018famconf_202008.dta'\n",
    "data_famconf = pd.read_stata(file_path_famconf, convert_categoricals=False) \n",
    "#如果报错：value labels for column wm703 are not unique.解决方法  convert_categoricals=False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fid18</th>\n",
       "      <th>fid_provcd18</th>\n",
       "      <th>fid_countyid18</th>\n",
       "      <th>fid_cid18</th>\n",
       "      <th>fid_urban18</th>\n",
       "      <th>pid</th>\n",
       "      <th>fid_base</th>\n",
       "      <th>psu</th>\n",
       "      <th>fid10</th>\n",
       "      <th>fid12</th>\n",
       "      <th>fid14</th>\n",
       "      <th>fid16</th>\n",
       "      <th>familysize18</th>\n",
       "      <th>subsample</th>\n",
       "      <th>subpopulation</th>\n",
       "      <th>genetype18</th>\n",
       "      <th>rtype_end18</th>\n",
       "      <th>code_a_p</th>\n",
       "      <th>tb6_a18_p</th>\n",
       "      <th>co_a18_p</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100051.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>624942.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100051501.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>100051.0</td>\n",
       "      <td>100051.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>501.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100051.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>624942.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110043107.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>100051.0</td>\n",
       "      <td>100051.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100051.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>624942.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100051502.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>100051.0</td>\n",
       "      <td>100051.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>502.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100160.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100160601.0</td>\n",
       "      <td>120009.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>601.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100160.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>120009102.0</td>\n",
       "      <td>120009.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>120009.0</td>\n",
       "      <td>120009.0</td>\n",
       "      <td>100160.0</td>\n",
       "      <td>100160.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      fid18  fid_provcd18  fid_countyid18  fid_cid18  fid_urban18  \\\n",
       "0  100051.0          11.0            45.0   624942.0          1.0   \n",
       "1  100051.0          11.0            45.0   624942.0          1.0   \n",
       "2  100051.0          11.0            45.0   624942.0          1.0   \n",
       "3  100160.0          12.0            79.0       -9.0          1.0   \n",
       "4  100160.0          12.0            79.0       -9.0          1.0   \n",
       "\n",
       "           pid  fid_base   psu     fid10     fid12     fid14     fid16  \\\n",
       "0  100051501.0  110043.0  45.0      -8.0      -8.0  100051.0  100051.0   \n",
       "1  110043107.0  110043.0  45.0  110043.0  110043.0  100051.0  100051.0   \n",
       "2  100051502.0  110043.0  45.0      -8.0      -8.0  100051.0  100051.0   \n",
       "3  100160601.0  120009.0  79.0      -8.0      -8.0      -8.0      -8.0   \n",
       "4  120009102.0  120009.0  79.0  120009.0  120009.0  100160.0  100160.0   \n",
       "\n",
       "   familysize18  subsample  subpopulation  genetype18  rtype_end18  code_a_p  \\\n",
       "0           3.0        1.0            6.0         0.0          1.0     501.0   \n",
       "1           3.0        1.0            6.0         1.0          1.0     107.0   \n",
       "2           3.0        1.0            6.0         0.0          1.0     502.0   \n",
       "3           2.0        1.0            6.0         0.0          4.0     601.0   \n",
       "4           2.0        1.0            6.0         1.0          1.0     102.0   \n",
       "\n",
       "   tb6_a18_p  co_a18_p  \n",
       "0        1.0       1.0  \n",
       "1        1.0       1.0  \n",
       "2        1.0       1.0  \n",
       "3        1.0       1.0  \n",
       "4        1.0       1.0  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_famconf.iloc[0:5,0:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_save(df, name):\n",
    "    df.to_csv('XXXXX/'+name, index = False)\n",
    "    print('done')\n",
    "    return \n",
    "\n",
    "def data_load(name):\n",
    "    return pd.DataFrame('XXXX'+name)\n",
    "\n",
    "df.to_csv('titanic2.csv', index = False )\n",
    "data_save(df,NameError)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存为excel 格式\n",
    "df.to_excel(os.path.join(data_save_path,'titanic_adjust.xlsx'),index=False, sheet_name = 'titanic')\n",
    "\n",
    "# DataFrame.to_excel(excel_writer, sheet_name='Sheet1', na_rep='',  float_format=None, columns=None, header=True, index=True, \n",
    "# index_label=None, startrow=0, startcol=0, engine=None,  merge_cells=True, encoding=None, inf_rep='inf', verbose=True, \n",
    "# freeze_panes=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 12 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   PassengerId  891 non-null    object \n",
      " 1   Survived     891 non-null    int64  \n",
      " 2   Pclass       891 non-null    int64  \n",
      " 3   Name         891 non-null    object \n",
      " 4   gender          891 non-null    object \n",
      " 5   Age          714 non-null    float64\n",
      " 6   SibSp        891 non-null    int64  \n",
      " 7   Parch        891 non-null    int64  \n",
      " 8   Ticket       891 non-null    object \n",
      " 9   Fare         891 non-null    float64\n",
      " 10  Cabin        204 non-null    object \n",
      " 11  Embarked     889 non-null    object \n",
      "dtypes: float64(2), int64(4), object(6)\n",
      "memory usage: 83.7+ KB\n"
     ]
    }
   ],
   "source": [
    "# df.info()\n",
    "df['PassengerId'] = df['PassengerId'].astype('str')\n",
    "df.describe().columns.tolist()\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 增加数据\n",
    "df2 = df.copy() # 复制一份数据\n",
    "df2 = df.copy(deep = True) # 复制一份数据\n",
    "\n",
    "# 增加1列\n",
    "df2['new_variable'] = list(range(891))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           a   b\n",
      "0     [5, 2]  10\n",
      "1     [2, 4]  11\n",
      "2  [1, 2, 3]  10\n",
      "           a   b\n",
      "0     [5, 2]  10\n",
      "1     [2, 4]  11\n",
      "2  [1, 2, 3]  10\n"
     ]
    }
   ],
   "source": [
    "# why deep = True, 如果dataframe中含有列表，使用浅copy对列表内容的修改也会影响原dataframe\n",
    "\n",
    "dict = {'a':[[1,2],[2,4],[1,2,3]],'b':[10,11,10]}\n",
    "a = pd.DataFrame(dict)\n",
    "a\n",
    "b = a.copy()\n",
    "b.iloc[0,0][0] = 5\n",
    "print(b)\n",
    "print(a)\n",
    "# b.iloc[0,1] = 50\n",
    "# print(b)\n",
    "# print(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gender_dummy</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender_dummy     gender\n",
       "0          0    male\n",
       "1          1  female\n",
       "2          1  female\n",
       "3          1  female\n",
       "4          0    male\n",
       "5          0    male\n",
       "6          0    male\n",
       "7          0    male\n",
       "8          1  female\n",
       "9          1  female"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()\n",
    "df['gender_dummy'] = np.where(df['gender']=='male', 0, 1)\n",
    "df[['gender_dummy','gender']][0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.copy(deep = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gender</th>\n",
       "      <th>gender_dummy2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>male</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>male</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>male</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888</th>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>male</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890</th>\n",
       "      <td>male</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>891 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        gender  gender_dummy2\n",
       "0      male           0\n",
       "1    female           1\n",
       "2    female           1\n",
       "3    female           1\n",
       "4      male           0\n",
       "..      ...         ...\n",
       "886    male           0\n",
       "887  female           1\n",
       "888  female           1\n",
       "889    male           0\n",
       "890    male           0\n",
       "\n",
       "[891 rows x 2 columns]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['gender_dummy2'] = df2['gender'].map(lambda x: 0 if x=='male' else 1)\n",
    "df2[['gender','gender_dummy2']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 指定位置添加列；注意：列名(不能与存在列名相同)\n",
    "\n",
    "df2.insert(2,'new_variable',range(891))  # 位置，新的变量名，变量\n",
    "#df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((891, 14), (894, 14))"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use append\n",
    "new = df2.iloc[9:12,:]\n",
    "df3 = df2.append(new,ignore_index=True)\n",
    "# True 按照新的数组的行索引排列\n",
    "# False 保持原有的 \n",
    "df2.shape, df3.shape\n",
    "# df3.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.drop(labels = ['new_variable','new_variable2','gender_num','gender_num2'], axis = 1, inplace = True)\n",
    "df3.columns\n",
    "\n",
    "# 同时删除多个变量，需要以列表的形式\n",
    "# inplace =True,代表是否对原数据操作, 否则返回的是视图，并没有对原数据进行操作\n",
    "# axis = 1代表删除列； axis = 0 表示删除行；默认 axis = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'gender', 'Age', 'SibSp',\n",
      "       'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked', 'gender_dummy',\n",
      "       'gender_dummy2'],\n",
      "      dtype='object')\n",
      "Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'gender', 'Age', 'SibSp',\n",
      "       'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(df3.columns)\n",
    "# df4 = df3.drop(columns = ['gender_dummy2', 'gender_dummy'], inplace = True)\n",
    "print(df3.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([22.,  2., 24.])"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df3[df3.Age.isin([22,24,2])]['Age'].unique()"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "b3ba2566441a7c06988d0923437866b63cedc61552a5af99d1f4fb67d367b25f"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
